from flask import Flask, request, jsonify
import pandas as pd
from sqlalchemy import create_engine
from flask_cors import CORS
import traceback  # 用于捕获完整的异常堆栈信息

app = Flask(__name__)
CORS(app, origins=["http://localhost:5173"])

# 数据库连接配置
config = {
    'user': 'root',
    'password': 'root',
    'host': 'localhost',
    'database': 'ticketingsystem'
}

# 创建 SQLAlchemy 引擎
engine = create_engine(f"mysql+mysqlconnector://{config['user']}:{config['password']}@{config['host']}/{config['database']}")

# 加载 CSV 文件中的数据
def load_labels():
    model_dir = 'Ticketing-System/server/model'
    data_path = f'{model_dir}/incident_labels.csv'
    try:
        df = pd.read_csv(data_path)
        print("Labels loaded successfully.")
        return df
    except Exception as e:
        print(f"Error loading labels: {e}")
        traceback.print_exc()
    return None

# 查询指定 id 的事件
def fetch_incident_by_id(incident_id):
    query = f"""
        SELECT 
            id,
            Inc_id,
            YEAR(Opened) AS year,
            WEEK(Opened, 1) AS week,  -- 使用模式 1
            Priority,
            Short_description,
            Assignment_group,
            Opened_by,
            Project,
            Assigned_to,
            Incident_state
        FROM incidents
        WHERE id = {incident_id}
    """
    try:
        df = pd.read_sql(query, engine)
        if not df.empty:
            return df.iloc[0].to_dict()
        else:
            return None
    except Exception as e:
        print(f"Error fetching incident by id: {e}")
        traceback.print_exc()
    return None

# 格式化事件统计结果
def format_incident_stats(incidents):
    formatted_results = {}
    for incident in incidents:
        year = incident['year']
        week = incident['week']
        priority = incident['Priority']
        week_key = f"{year}-{week}"

        if week_key not in formatted_results:
            formatted_results[week_key] = {
                "year": year,
                "week": week,
                "priorities": {
                    '1 - Critical': {'total': 0, 'closedResolved': 0, 'inProgress': 0},
                    '2 - High': {'total': 0, 'closedResolved': 0, 'inProgress': 0},
                    '3 - Moderate': {'total': 0, 'closedResolved': 0, 'inProgress': 0},
                    '4 - Low': {'total': 0, 'closedResolved': 0, 'inProgress': 0},
                }
            }

        formatted_results[week_key]['priorities'][priority]['total'] += 1
        if incident['Incident_state'] in ['Closed', 'Resolved']:
            formatted_results[week_key]['priorities'][priority]['closedResolved'] += 1
        elif incident['Incident_state'] in ['Work In Progress', 'Open', 'Awaiting User Info']:
            formatted_results[week_key]['priorities'][priority]['inProgress'] += 1

    return formatted_results

# API 接口：分类事件
@app.route('/api/classifyincidents', methods=['POST'])
def classify_incidents_api():
    try:
        data = request.json
        year = data.get('year')
        week = data.get('week')
        project = data.get('project')

        if not all([year, week, project]):
            return jsonify({'error': 'Missing required fields: year, week, project'}), 400

        # 加载标签数据
        labels_df = load_labels()
        if labels_df is None:
            return jsonify({'error': 'No labels found'}), 404

        # 查询完整事件数据
        incidents = []
        for _, row in labels_df.iterrows():
            incident_id = row['id']
            incident_data = fetch_incident_by_id(incident_id)
            if incident_data:
                incident_data['is_real'] = int(row['label'])  # 转换为 Python 原生整数
                incidents.append(incident_data)

        # 筛选指定周的真实事件和虚假事件
        real_incidents = [
            incident for incident in incidents
            if incident['year'] == year and incident['week'] == week and incident['Project'] == project and incident['is_real'] == 1
        ]
        fake_incidents = [
            incident for incident in incidents
            if incident['year'] == year and incident['week'] == week and incident['Project'] == project and incident['is_real'] == 0
        ]

        return jsonify({
            'realIncidents': real_incidents,
            'fakeIncidents': fake_incidents
        })
    except Exception as e:
        print(f"Error in classify_incidents_api: {e}")
        traceback.print_exc()
        return jsonify({'error': str(e)}), 500

# API 接口：获取格式化后的事件统计
@app.route('/api/incidentstats', methods=['POST'])
def get_incident_stats():
    try:
        data = request.json
        year = data.get('year')
        week = data.get('week')
        project = data.get('project')

        if not all([year, week, project]):
            return jsonify({'error': 'Missing required fields: year, week, project'}), 400

        # 加载标签数据
        labels_df = load_labels()
        if labels_df is None:
            return jsonify({'error': 'No labels found'}), 404

        # 查询完整事件数据
        incidents = []
        for _, row in labels_df.iterrows():
            incident_id = row['id']
            incident_data = fetch_incident_by_id(incident_id)
            if incident_data:
                incident_data['is_real'] = int(row['label'])  # 转换为 Python 原生整数
                incidents.append(incident_data)

        # 筛选指定周的真实事件
        real_incidents = [
            incident for incident in incidents
            if incident['year'] == year and incident['week'] == week and incident['Project'] == project and incident['is_real'] == 1
        ]

        # 格式化查询结果
        formatted_results = format_incident_stats(real_incidents)
        return jsonify(formatted_results)
    except Exception as e:
        print(f"Error in get_incident_stats: {e}")
        traceback.print_exc()
        return jsonify({'error': str(e)}), 500

if __name__ == '__main__':
    app.run(debug=True)